import pymysql
'''
连接数据库工具
'''

class TryDButils:  # 定义一个名为TryDButils类，用来管理数据的操作

    # 初始化方法，设置连接数据库的信息
    def __init__(self, host='localhost', port=3306, user='root', password='root', database='finance',):
        self.host = host
        self.port = port
        self.user = user
        self.password = password  # 将password参数赋值给实例变量怕password
        self.database = database
        self.connection = None  # 初始化连接对象为None
        self.cursor = None  # 初始化游标对象None

    def connect(self):  # 定义链接数据库的方法
        try:
            self.connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
            )

            self.cursor = self.connection.cursor()  # 获取游标对象
        except Exception as e:
            print(f"连接错误：{e}")  # 打印数据库连接错误的信息
            self.cursor.close()

    def close(self):  # 定义关闭数据库连接的方法
        if self.cursor:  # 如果游标对象存在就关闭
            self.cursor.close()
        if self.connection:  # 如果连接对象存在就关闭
            self.connection.close()

    def execute(self, sql, params=None):  # 定义执行sql的方法
        try:
            if not self.connection or not self.cursor:  # 如果连接对象或者游标不存在
                self.connect()  # 调用数据库连接方法

            self.cursor.execute(sql, params)  # 执行sql语句
            self.connection.commit()  # 提交事务

        except Exception as e:
            print(f"执行失败，错误信息如下：{e}")
            self.close()

    def update(self, sql, params=None):  # 定义更新数据的方法
        self.execute(sql, params)  # 执行sql语句的方法

    def select(self, sql, params=None):  # 定义查询数据的方法
        self.execute(sql, params)  # 执行sql语句的方法

        try:
            return self.cursor.fetchall()  # 返回结果集
        except Exception as e:
            print(f"查询异常，报错如下：{e}")


# if __name__ == "__main__":
#     db = TryDButils()
#     print(db.select("select username,password from user"))
#     # value = "(80, 'ACCOUNTING', 'NEW YORK'),(90, 'ACCOUNTING', 'NEW YORK'),(91, 'ACCOUNTING', 'NEW YORK'),(92, 'ACCOUNTING', 'NEW YORK')"
#     # sql = f"INSERT INTO dept VALUES {value}"
#     # db.update(sql, )
#
#     # delete_sql = "delete from dept where DEPTNO = 92"
#     # db.update(delete_sql, )
#
#     # sellect_sql = "select * from user"
#     # rusult = db.select(sellect_sql)
#     # for r in rusult:
#     #     print(r)
#     db.update("insert into address values(9,'2', '2', '2', 15)")
